package com.lin.cms.ksmg;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * 
 *     
 * 类描述：   操作sql类
 * 创建人：luanzelin   
 * 创建时间：2013-5-25 下午3:38:06    
 *
 */
public class CreateBean {
	private Connection connection = null;
	static String url;   
	static String username ;
	static String password ;
	static String rt="\r\t";
	String SQLTables="show tables";
	static{
	try{
		Class.forName("com.mysql.jdbc.Driver");
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * 方法名称: setMysqlInfo 
	 * 方法描述: TODO(放入数据库连接地址，用户名，密码) 
	 *
	 * @param url
	 * @param username
	 * @param password 
	 *
	 * 创建人：luanzelin   
	 * 创建时间：2013-5-25 下午3:40:41
	 *
	 */
	@SuppressWarnings("static-access")
	public void setMysqlInfo(String url,String username,String password){
		   this.url=url;
		   this.username=username;
		   this.password=password;
	}
	public void setConnection(Connection connection) {
		this.connection = connection;
	}
	public Connection getConnection() throws SQLException{
		return DriverManager.getConnection(url, username, password);
	}
    public List<String> getTables() throws SQLException{
    	Connection con=this.getConnection();
    	PreparedStatement ps= con.prepareStatement(SQLTables);
    	ResultSet rs=ps.executeQuery();
    	List<String> list=new ArrayList<String>();
    	while(rs.next()){
    		String tableName=rs.getString(1);
    		list.add(tableName);
    	}
    	rs.close();
		ps.close();
		con.close();
		return list;
	}
    
    /**
     * 查询表的字段，封装成List
     * @param tableName
     * @return
     * @throws SQLException
     */
    public List<ColumnData> getColumnDatas(String tableName) throws SQLException{
    	String SQLColumns="SELECT distinct COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM information_schema.columns WHERE table_name =  '"+tableName+"' ";
        Connection con=this.getConnection();
    	PreparedStatement ps=con.prepareStatement(SQLColumns);
    	List<ColumnData>  columnList= new ArrayList<ColumnData>();
        ResultSet rs=ps.executeQuery();
        StringBuffer str = new StringBuffer();
		StringBuffer getset=new StringBuffer();
        while(rs.next()){
        	String name = rs.getString(1);
			String type = rs.getString(2);
			String comment = rs.getString(3);
			type=this.getType(type);
			
			ColumnData cd= new ColumnData();
			cd.setColumnName(name);
			cd.setDataType(type);
			cd.setColumnComment(comment);
			cd.setDomainName(getCulumnNameToDomainName(name));
			columnList.add(cd);
        }
        argv=str.toString();
        method=getset.toString();
		rs.close();
		ps.close();
		con.close();
		return columnList;
    }
    
    private String method;
    private String argv;
    
    /**
     * 生成实体Bean 的属性和set,get方法
     * @param tableName
     * @return
     * @throws SQLException
     */
    public String getBeanFeilds(String tableName) throws SQLException{
    	String className=getTablesNameToClassName(tableName);
    	List<ColumnData> dataList = getColumnDatas(tableName);
    	StringBuffer str = new StringBuffer();
    	StringBuffer getset = new StringBuffer();
    	StringBuffer constructor=new StringBuffer();
    	StringBuffer hashCode=new StringBuffer();
    	StringBuffer equals=new StringBuffer();
    	StringBuffer toString=new StringBuffer();
    	constructor.append("\r\t").append("public"+" ").append(className).append("(){"+"\r\t"+"}");
    	
        for(int i=0;i<dataList.size();i++){
        	String name = getCulumnNameToDomainName(dataList.get(i).getColumnName());
        	if(i==0){
        		hashCode.append("\r\t"+"@Override"+"\r\t"+"public int hashCode() {"+"\r\t"+"    final int prime = 31;"+"\r\t"+"    int result = 1;"+"\r\t"+"    result = prime * result + "+name+";")
        		.append("\r\t").append("    return result;"+"\r\t"+"}");
        		equals.append("\r\t"+"@Override"+"\r\t"+"public boolean equals(Object obj) {")
        		.append("\r\t"+"    if (this == obj)"+"\r\t"+"        return true;"+"\r\t"+"    if (obj == null)"+"\r\t"+"        return false;")
        		.append("\r\t"+"    if (getClass() != obj.getClass())"+"\r\t"+"        return false;")
        		.append("\r\t"+"    final "+className+" other = ("+className+") obj;")
        		.append("\r\t"+"    if ("+name +"!= other."+name+")").append("\r\t"+"        return false;")
        		.append("\r\t"+"\r\t").append("    return true;"+"\r\t"+"}");
        		
        		toString.append("\r\t"+"@Override"+"\r\t"+"public String toString() {"+"\r\t")
        		.append("    return getClass().getName() + \"@\" + Integer.toHexString(hashCode()) + ")
        		.append("\r\t"+"        \"(\""+"+")
        		.append("\r\t"+"        \""+name+"=\""+"+")
        		.append("\"\'\""+"+"+name+"+"+"\"\'\""+"+")
        		.append("\r\t"+"        \")\";").append("\r\t"+"}");
        	}
			String type =  dataList.get(i).getDataType();
			String maxChar=name.substring(0, 1).toUpperCase();
			str.append("\r\t").append("private ").append(type+" ").append(name).append(";");
			String method=maxChar+name.substring(1, name.length());
			getset.append("\r\t").append("public ").append(type+" ").append("get"+method+"() {\r\t");
			getset.append("    return this.").append(name).append(";\r\t}");
			getset.append("\r\t").append("public void ").append("set"+method+"("+type+" "+name+") {\r\t");
			getset.append("    this."+name+"=").append(name).append(";\r\t}");
        }
        argv=str.toString();
        method=getset.toString();
		return argv+constructor+method+hashCode+equals+toString;
    }
    /**
     * 
     * <br>
     * <b>功能：</b>详细的功能描述<br>
     * <b>作者：</b>luanzelins<br>
     * <b>日期：</b> 2011-12-20 <br>
     * @param tableName
     * @return 
     * @throws SQLException
     */
//    public List<Map<String,String>> getColumnsMap(String tableName) throws SQLException{
//    	String SQLColumns="SELECT distinct COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT FROM information_schema.columns WHERE  table_schema = 'ssi' and table_name =  '"+tableName+"' ";
////    	String SQLColumns="desc "+tableName;
//    	Connection con=this.getConnection();
//    	PreparedStatement ps=con.prepareStatement(SQLColumns);
//    	List<Map<String,String>> listMap=new ArrayList<Map<String,String>>();
//        ResultSet rs=ps.executeQuery();
//        while(rs.next()){
//        	Map<String,String> columnsMap=new HashMap<String, String>();
////        	String name = rs.getString(1);
////			String type = rs.getString(2);
////			String comment = rs.getString(3);
//			
//			
//			String name = rs.getString(1);
//			String type = rs.getString(2);
//			String comment = rs.getString(3);
//			type=this.getType(type);
//			columnsMap.put("COLUMN_NAME", name);
//			columnsMap.put("DATA_TYPE", type);
//			columnsMap.put("COLUMN_COMMENT", comment);
//			listMap.add(columnsMap);
//        }
//		rs.close();
//		ps.close();
//		con.close();
//		return listMap;
//    }
    public String getType(String type){
    	type=type.toLowerCase();
    	if("char".equals(type) || "varchar".equals(type)){
			return "String";
		}else if("int".equals(type) ){
			return "Integer";
		}else if("tinyint".equals(type)){
			return "Boolean";
		}else if("bigint".equals(type)){
			return "Long";
		}else if("timestamp".equals(type) || "date".equals(type)  || "datetime".equals(type)){
			return "Date";
		}
    	return null;
    }
    public void getPackage(int type,String createPath, String content,String packageName,String className,String extendsClassName,String ... importName) throws Exception{
    	if(null==packageName){
    		packageName="";
    	}
    	StringBuffer sb=new StringBuffer();
    	sb.append("package ").append(packageName).append(";\r");
    	sb.append("\r");
    	for(int i=0;i<importName.length;i++){
    		sb.append("import ").append(importName[i]).append(";\r");
    	}
    	sb.append("\r");
    	sb.append("/**\r *  entity. @author wolf Date:"+new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())+"\r */");
    	sb.append("\r");
    	sb.append("\rpublic class ").append(className);
    	if(null!=extendsClassName){
    		sb.append(" extends ").append(extendsClassName);
    	}
    	if(type==1){ //bean
    	   sb.append(" ").append("implements java.io.Serializable {\r");
    	}else{
    		sb.append(" {\r");
    	}
    	sb.append("\r\t");
    	sb.append("private static final long serialVersionUID = 1L;\r\t");
    	String temp=className.substring(0, 1).toLowerCase();
    	temp+= className.substring(1, className.length());
    	if(type==1){
    	sb.append("private "+className+" "+temp+"; // entity ");
    	}
    	sb.append(content);
    	sb.append("\r}");
    	System.out.println(sb.toString());
    	this.createFile(createPath, "", sb.toString());
    }
   
   /**
    * 
    * 方法名称: getTablesNameToClassName 
    * 方法描述: TODO(表名转换成类名 每_首字母大写) 
    *
    * @param tableName
    * @return 
    *
    * 创建人：luanzelin   
    * 创建时间：2013-5-25 下午3:42:35
    *
    */
    public String getTablesNameToClassName(String tableName){
    	String [] split=tableName.split("_");
    	if(split.length>1){
    		StringBuffer sb=new StringBuffer();
            for(int i=0;i<split.length;i++){
            	String tempTableName=split[i].substring(0, 1).toUpperCase()+split[i].substring(1, split[i].length());
                sb.append(tempTableName);
            }
            System.out.println(sb.toString());
            return sb.toString();
    	}else{
    		String tempTables=split[0].substring(0, 1).toUpperCase()+split[0].substring(1, split[0].length());
    		return tempTables;
    	}
    }
    
    /**
     * 
     * 方法名称: getCulumnNameToDomainName 
     * 方法描述: TODO(把列名换成实体类名) 
     *
     * @param culumnName
     * @return 
     *
     * 创建人：luanzelin   
     * 创建时间：2013-5-25 下午3:42:35
     *
     */
     public String getCulumnNameToDomainName(String culumnName){
     	String [] split=culumnName.split("_");
     	if(split.length>1){
     		StringBuffer sb=new StringBuffer();
     		String tempTableName=null;
             for(int i=0;i<split.length;i++){
            	 if(i==0){
            		 tempTableName=split[0];
            	 }else{
            		 tempTableName=split[i].substring(0, 1).toUpperCase()+split[i].substring(1, split[i].length());
            	 }
                 sb.append(tempTableName);
             }
             System.out.println(sb.toString());
             return sb.toString();
     	}else{
     		String tempTables=split[0];
     		return tempTables;
     	}
     }
     
    /**
     * 
     * <br>
     * <b>功能：</b>创建文件<br>
     * <b>作者：</b>肖财高<br>
     * <b>日期：</b> 2011-12-21 <br>
     * @param path
     * @param fileName
     * @param str
     * @throws IOException
     */
   public void createFile(String path,String fileName,String str) throws IOException{
    	FileWriter writer=new FileWriter(new File(path+fileName));
    	writer.write(new String(str.getBytes("utf-8")));
    	writer.flush();
    	writer.close();
    }
   	
    static String selectStr="select ";
    static String from=" from ";
    
    /**
     * 
     * 方法名称: getAutoCreateSql 
     * 方法描述: TODO(生成sql语句) 
     *
     * @param tableName
     * @return
     * @throws Exception 
     *
     * 创建人：luanzelin   
     * 创建时间：2013-5-25 下午3:57:34
     *
     */
    public Map<String,Object> getAutoCreateSql(String tableName) throws Exception{
   	 	 Map<String,Object> sqlMap=new HashMap<String, Object>();
   	 	 List<ColumnData> columnDatas = getColumnDatas(tableName);
	     String columns=this.getColumnSplit(columnDatas);
	     String columnsDomain=this.getColumnDomainSplit(columnDatas);
	     String[] columnList =  getColumnList(columns);  //表所有字段
	     String columnFields =  getColumnFields(columns); //表所有字段 按","隔开
	     String insert="insert into "+tableName+"("+columns.replaceAll("\\|", ",")+")\n\tvalues(#{"+columnsDomain.replaceAll("\\|", "},#{")+"})";
	     String update= getUpdateSql(tableName, columnList);
	     String updateSelective = getUpdateSelectiveSql(tableName, columnDatas);
	     String selectById = getSelectByIdSql(tableName, columnList);
	     String delete = getDeleteSql(tableName, columnList);
	     sqlMap.put("columnList",columnList);
	     sqlMap.put("columnFields",columnFields);
	     sqlMap.put("insert", insert);
	     sqlMap.put("update", update);
	     sqlMap.put("delete", delete);
	     sqlMap.put("updateSelective", updateSelective);
	     sqlMap.put("selectById", selectById);
	     return sqlMap;
   }
    
    /**
     * delete 
     * @param tableName
     * @param columnsList
     * @return
     * @throws SQLException
     */
    public String getDeleteSql( String tableName,String[] columnsList)throws SQLException{
   	 StringBuffer sb=new StringBuffer();
   	 sb.append("delete");
	     sb.append(" from ").append(tableName).append("\r\t"+"where ");
	     sb.append(columnsList[0]).append(" = #{").append(getCulumnNameToDomainName(columnsList[0])).append("}");
   	return sb.toString();
   }
    
    /**
     * 根据id查询
     * @param tableName
     * @param columnsList
     * @return
     * @throws SQLException
     */
    public String getSelectByIdSql( String tableName,String[] columnsList)throws SQLException{
    	 StringBuffer sb=new StringBuffer();
    	 sb.append("select"+"\r\t"+"<include refid=\"Base_Column_List\" /> \n");
 	     sb.append("\tfrom ").append(tableName).append(" where ");
 	     sb.append(columnsList[0]).append(" = #{").append(getCulumnNameToDomainName(columnsList[0])).append("}");
    	return sb.toString();
    }
    
    /**
     * 获取所有的字段，并按","分割
     * @param columns
     * @return
     * @throws SQLException
     */
    public String getColumnFields(String columns)throws SQLException{
    	String fields = columns;
    	if(fields != null && !"".equals(fields)){
    		fields = fields.replaceAll("[|]", ",");
    	}
    	return fields;
    }
    
    /**
     * 
     * @param columns
     * @return
     * @throws SQLException
     */
    public String[] getColumnList(String columns)throws SQLException{
    	 String[] columnList=columns.split("[|]");
	     return columnList;
    }
    
    /**
     * 修改记录
     * @param tableName
     * @param columnsList
     * @return
     * @throws SQLException
     */
    public String getUpdateSql(String tableName,String[] columnsList)throws SQLException{
    	 StringBuffer sb=new StringBuffer();
    	 
	     for(int i=1;i<columnsList.length;i++){
	    	  String column=columnsList[i];
	    	  sb.append(column+"=#{"+getCulumnNameToDomainName(column)+"}");
	    	  //最后一个字段不需要","
	    	  if((i+1) < columnsList.length){
	    		  sb.append(","+"\r\t");
	    	  }
	    	  else{
	    		  sb.append("\r\t");
	    	  }
	    	  
	     }
    	 String update = "update "+tableName+"\r\tset\r\t"+sb.toString()+"where "+columnsList[0]+"=#{"+getCulumnNameToDomainName(columnsList[0])+"}";
	     return update;
    }
    
    public String getUpdateSelectiveSql(String tableName,List<ColumnData> columnList)throws SQLException{
   	 StringBuffer sb=new StringBuffer();
   	    ColumnData cd = columnList.get(0); //获取第一条记录，主键
   	 	sb.append("\t<set >\n");
   	 	 for(int i=1;i<columnList.size();i++){
   	 		 ColumnData data = columnList.get(i);
   	 		 String columnName=data.getColumnName();
   	 		 sb.append("\t  <if test=\"").append(getCulumnNameToDomainName(columnName)).append(" != null ");
   	 		 //String 还要判断是否为空
   	 		 if("String" == data.getDataType()){
   	 			sb.append(" and ").append(getCulumnNameToDomainName(columnName)).append(" != ''");
   	 		 }
   	 		 sb.append(" \">\n\t\t");
   	 		 sb.append(columnName+"=#{"+getCulumnNameToDomainName(columnName)+"},\n");
   	 		 sb.append("\t  </if>\n");
	     }
	     sb.append("\t</set>");
	     String update = "update "+tableName+" set \n"+sb.toString()+"\r\twhere "+cd.getColumnName()+"=#{"+getCulumnNameToDomainName(cd.getColumnName())+"}";
	     return update;
   }
   
    
    
   /**
    * 
    * 方法名称: getColumnSplit 
    * 方法描述: TODO(获取所有列名字) 
    *
    * @param columnList
    * @return
    * @throws SQLException 
    *
    * 创建人：luanzelin   
    * 创建时间：2013-5-25 下午4:02:49
    *
    */
    public String getColumnSplit(List<ColumnData> columnList) throws SQLException{
 	     StringBuffer commonColumns=new StringBuffer();
 	     for(ColumnData data : columnList){
 	    	 commonColumns.append(data.getColumnName()+"|");
 	     }
 	     return commonColumns.delete(commonColumns.length()-1, commonColumns.length()).toString();
    }
    
    public String getColumnDomainSplit(List<ColumnData> columnList) throws SQLException{
	     StringBuffer commonColumns=new StringBuffer();
	     for(ColumnData data : columnList){
	    	 commonColumns.append(data.getDomainName()+"|");
	     }
	     return commonColumns.delete(commonColumns.length()-1, commonColumns.length()).toString();
   }
    
}
